package com.ys.controller.api.business;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.ys.entity.business.TpShopAnalysisReportForms;
import com.ys.entity.hr.ThrOperateCompanyApply;
import com.ys.entity.sys.YsCol;
import com.ys.service.business.TpShopAnalysisReportFormsService;
import com.ys.service.sys.CommonApiService;
import com.ys.service.sys.UploadService;
import com.ys.util.DateUtil;
import com.ys.util.ExportExcelUtils;
import com.ys.vo.LayuiTableVoJA;
import com.ys.vo.R;

@Controller
@RequestMapping("/api/inter/tpShopAnalysisReportForms")
public class ApiInterTpShopAnalysisReportFormsController {
    private static final Logger logger = LoggerFactory.getLogger(ApiInterTpShopAnalysisReportFormsController.class);

    private static Long MENU_ID_BY_SHOP = 63L;
    private static Long MENU_ID_BY_COMPANY = 65L;
    private static Long MENU_ID_BY_USER = 66L;

	@Autowired
	private CommonApiService<TpShopAnalysisReportForms> commonApiService;
    @Autowired
    private TpShopAnalysisReportFormsService tpShopAnalysisReportFormsService;

    /**
     * 设置查询的cond的sql设置
     * @param cond
     * @return
     */
    public TpShopAnalysisReportForms setSeachSql(TpShopAnalysisReportForms cond){
        cond.addField("O.VGsJc AS vGsJc");//运营公司简称
        cond.addField("S.ShopName AS shopName ");//店铺名称
        cond.addField("O.CUser + '/' + U.UserName AS operateUser ");//运营人
        cond.addField("UO.DeptName AS vYyJb");//运营组别，所属部门
        cond.addField("CASE WHEN PO.CUser IS NULL THEN '' ELSE PO.CUser + '/' + PU.UserName END pOperateUser ");//运营人上级
        cond.addField("PUO.DeptName AS pVYyJb ");//运营组别上级，所属部门上级
        cond.addField("S.FirstSaleDay AS firstSaleDay ");//开售日期

        //关联运营公司备案表
        cond.addLeftJoin("kjecp.dbo.THR_OperateCompanyApply O ON O.VGsBm = A.CompanyGuid ");
        //关联运营公司备案表-上级
        //cond.addLeftJoin("kjecp.dbo.THR_OperateCompanyApply PO ON PO.VGsBm = O.VGsBmP ");
        cond.addLeftJoin("kjecp.dbo.TS_UserInfo U ON U.UserId = O.Cuser");
        cond.addLeftJoin("kjecp.dbo.TS_OrganizationInfo UO ON UO.Orgid = U.DeptId");

        //关联运营公司管理表-上级
        cond.addLeftJoin("kjecp.dbo.TS_OperateOrg ORG ON ORG.Id = O.OperateOrgId ");
        cond.addLeftJoin("kjecp.dbo.THR_OperateCompanyApply PO ON PO.VGsBm = ORG.HeadVgsbm AND 1=1 ");
        cond.addLeftJoin("kjecp.dbo.TS_UserInfo PU ON PU.UserId = PO.Cuser AND 1=1 ");
        cond.addLeftJoin("kjecp.dbo.TS_OrganizationInfo PUO ON PUO.Orgid = PU.DeptId");

        //关联网店信息表
        cond.addLeftJoin("kjecp.dbo.TP_AmazonShop S ON S.Guid = A.ShopGuid ");
        cond.where("A.TotalOrder > 0 ");//仅显示总订单＞0的店铺
        //排序
        cond.setOrderby("A.InfoTime DESC, A.CompanyGuid ASC");
        //权限
		cond = commonApiService.dataAuth(MENU_ID_BY_SHOP, cond);
        return  cond;
    }

    /**
     * 设置查询的cond的sql设置
     * @param cond
     * @return
     */
    public TpShopAnalysisReportForms setSeachSqlByCompany(TpShopAnalysisReportForms cond){
        String mainTable = "SELECT DISTINCT " +
                " '' AS Id,'' AS ShopGuid, '' AS UserId, '' AS DeptId, '' AS RecoveryTime, '' AS RecoveryCycle, '' AS Status, '' AS CreateTime, '' AS UpdateTime,"+
                "C.CompanyGuid," +
                "C.InfoTime," +
                "C.Prepayment," +
                "C.StartSubmitTime," +
                "C.TotalSubmitMoney," +
                "C.MonthFirstMoney," +
                "B.totalOrder," +
                "B.totalSale," +
                "C.AmazonBack," +
                "C.MeritsManage," +
                "C.MeritsAllocation,"+
                "C.ProcureCost," +
                "C.FirstLegCost," +
                "B.LogisticsCost," +
                "C.StorageCost," +
                "C.PackCost," +
                "C.MonthDay," +
                "B.FastMailCost, " +
                "C.OrgName "+
                "FROM ( SELECT SUM ( TotalOrder ) AS totalOrder, SUM ( TotalSale ) AS totalSale,  SUM ( LogisticsCost ) AS logisticsCost, SUM ( FastMailCost ) AS fastMailCost, CompanyGuid, InfoTime FROM kjecp_report.dbo.TP_ShopAnalysisReportForms GROUP BY CompanyGuid,InfoTime ) B " +
                "LEFT JOIN  kjecp_report.dbo.TP_ShopAnalysisReportForms C ON C.CompanyGuid = B.CompanyGuid " +
                "WHERE C.InfoTime = B.InfoTime";
        cond.setMainTable("("+mainTable+")");
        cond.addField("O.VGsJc AS vGsJc");//运营公司简称

        //关联运营公司备案表
        cond.addLeftJoin("kjecp.dbo.THR_OperateCompanyApply O ON O.VGsBm = A.CompanyGuid ");
        //排序
        cond.setOrderby("A.InfoTime DESC, A.CompanyGuid ASC");
        //权限
		cond = commonApiService.dataAuth(MENU_ID_BY_COMPANY, cond);
        return  cond;
    }

    /**
     * 设置查询的cond的sql设置
     * @param cond
     * @return
     */
    public TpShopAnalysisReportForms setSeachSqlByUser(TpShopAnalysisReportForms cond){
        String mainTable = "SELECT " +
                "SUM ( F.TotalOrder ) AS TotalOrder," +
                "SUM ( F.TotalSale ) AS TotalSale," +
                "SUM ( F.LogisticsCost ) AS LogisticsCost," +
                "SUM ( F.FastMailCost ) AS FastMailCost," +
                "SUM ( F.Prepayment ) AS Prepayment," +
                "SUM ( F.TotalSubmitMoney ) AS TotalSubmitMoney," +
                "SUM ( F.MonthFirstMoney ) AS MonthFirstMoney," +
                "SUM ( F.AmazonBack ) AS AmazonBack," +
                //"F.MeritsManage," +
                "SUM ( F.MeritsAllocation ) AS MeritsAllocation," +
                "SUM ( F.ProcureCost ) AS ProcureCost," +
                "SUM ( F.FirstLegCost ) AS FirstLegCost," +
                "SUM ( F.StorageCost ) AS StorageCost," +
                "SUM ( F.PackCost ) AS PackCost," +
                "F.StartSubmitTime," +
                "F.MonthDay," +
                "F.UserId," +
                "F.InfoTime, " +
                "''AS OrgName,'' AS MeritsManage,'' AS CompanyGuid,'' AS Id,'' AS ShopGuid,'' AS DeptId,'' AS RecoveryTime,'' AS RecoveryCycle,'' AS Status,'' AS CreateTime,'' AS UpdateTime " +
                "FROM( " +
                "SELECT " +
                "SUM( S.TotalOrder ) AS TotalOrder," +
                "SUM ( S.TotalSale ) AS TotalSale," +
                "SUM ( S.LogisticsCost ) AS LogisticsCost," +
                "SUM ( S.FastMailCost ) AS FastMailCost," +
                "S.CompanyGuid," +
                "S.InfoTime," +
                "S.UserId," +
                "S.Prepayment, " +
                "S.StartSubmitTime," +
                "S.TotalSubmitMoney," +
                "S.MonthFirstMoney," +
                "S.AmazonBack," +
                //"S.MeritsManage," +
                "S.MeritsAllocation, "+
                "S.ProcureCost," +
                "S.FirstLegCost," +
                "S.StorageCost," +
                "S.PackCost," +
                "S.MonthDay " +
                "FROM " +
                "kjecp_report.dbo.TP_ShopAnalysisReportForms S " +
                "GROUP BY " +
                "S.CompanyGuid,S.InfoTime," +
                "S.UserId,S.Prepayment,S.StartSubmitTime,S.TotalSubmitMoney,S.MonthFirstMoney,S.AmazonBack," +
                "S.MeritsAllocation,S.ProcureCost,S.FirstLegCost,S.StorageCost,S.PackCost,S.MonthDay" +
                ") F  " +
                //"GROUP BY F.UserId ,F.StartSubmitTime,F.MonthDay,F.InfoTime,F.MeritsManage";
                "GROUP BY F.UserId ,F.StartSubmitTime,F.MonthDay,F.InfoTime";

        cond.setMainTable("("+mainTable+")");
        cond.addField("U.UserId + '/' + U.UserName AS operateUser");//用户名称
        cond.addField("U.DeptName AS vYyJb");//运营组别，所属部门
        cond.addField("U.CidName2 AS vGsJc");//运营组别公司简称
        cond.addField("'运营组长' AS userWork");//运营人岗位

        //关联用户角色
        cond.addLeftJoin("kjecp.dbo.TS_UserInfo U ON A.UserId = U.UserId ");
        cond.addLeftJoin("kjecp.dbo.TS_RoleInfo R ON U.PositionGuid = R.Guid ");
        //过滤 绩效大于90%，并且是运营组长的
        //cond.where("A.MeritsManage >= 90 AND R.RoleName='运营组长'");
        cond.where("R.RoleName='运营组长'");
        //排序
        cond.setOrderby("A.InfoTime DESC, A.UserId ASC");
        //权限
		cond = commonApiService.dataAuth(MENU_ID_BY_USER, cond);
        return  cond;
    }


    /*
    public TpShopAnalysisReportForms setSeachSqlByMainUser(TpShopAnalysisReportForms cond){

        String mainTable = "SELECT " +
                //"MAX(F.MeritsManage) AS MeritsManage," +
                "'' AS FastMailCost,'' AS LogisticsCost,'' AS TotalSale,'' AS TotalOrder,'' AS CompanyGuid,'' AS Id,'' AS ShopGuid,'' AS RecoveryTime,'' AS RecoveryCycle,'' AS Status,'' AS CreateTime,'' AS UpdateTime, " +
                "'' AS Prepayment,'' AS TotalSubmitMoney,'' AS FirstLegCost,'' AS MonthFirstMoney,'' AS AmazonBack,'' AS MeritsAllocation,'' AS ProcureCost,'' AS StorageCost,'' AS PackCost,'' AS StartSubmitTime,'' AS MonthDay, " +
                "F.UserId," +
                "F.InfoTime, " +
                "F.DeptId " +
                "FROM " +
                "kjecp_report.dbo.TP_ShopAnalysisReportForms F " +
                "GROUP BY " +
                "F.UserId,F.InfoTime,F.DeptId" ;
        cond.setMainTable("("+mainTable+")");
        //关联用户角色
        cond.addLeftJoin("TS_UserInfo U ON A.UserId = U.UserId ");
        cond.addLeftJoin("TS_RoleInfo R ON U.PositionGuid = R.Guid ");
        //过滤 绩效大于90%，并且是运营组长的
        //cond.where("A.MeritsManage >= 90 AND R.RoleName='运营组长'");
        cond.where("R.RoleName='运营组长'");
        //排序
        cond.setOrderby("A.InfoTime DESC, A.UserId ASC");
        //权限
		cond = commonApiService.dataAuth(MENU_ID_BY_USER, cond);
        return  cond;
    }

     */
    public TpShopAnalysisReportForms setSeachSqlByMainUser(TpShopAnalysisReportForms cond){
        /*
        String mainTable = "SELECT\n" +
                "\t'' AS Id,'' AS ShopGuid,'' AS RecoveryTime,'' AS RecoveryCycle,'' AS MeritsManage,'' AS Status,'' AS CreateTime,'' AS UpdateTime,'' AS deptId,AL.* \n" +
                "FROM (\n" +
                "\tSELECT\n" +
                "\t\tD.*,U.UserId + '/' + U.UserName AS operateUser,U.UserId AS userId,U.GuidYsRole AS userWork,2 AS pageType ,O.VGsJc AS vGsJc\n" +
                "\tFROM (\n" +
                "\t\t\tSELECT \n" +
                "\t\t\t\tSUM ( F.TotalOrder ) AS totalOrder,SUM ( F.TotalSale ) AS totalSale,SUM ( F.LogisticsCost ) AS logisticsCost,SUM ( F.FastMailCost ) AS fastMailCost,\n" +
                "\t\t\t\tSUM ( F.Prepayment ) AS prepayment,SUM ( F.TotalSubmitMoney ) AS totalSubmitMoney,SUM ( F.MonthFirstMoney ) AS monthFirstMoney,SUM ( F.AmazonBack ) AS amazonBack,\n" +
                "\t\t\t\tSUM ( F.MeritsAllocation ) AS meritsAllocation,SUM ( F.ProcureCost ) AS procureCost,SUM ( F.FirstLegCost ) AS firstLegCost,SUM ( F.StorageCost ) AS storageCost,\n" +
                "\t\t\t\tSUM ( F.PackCost ) AS packCost,MIN ( F.StartSubmitTime ) AS startSubmitTime,F.MonthDay AS monthDay,F.InfoTime AS infoTime ,\n" +
                "\t\t\t\tF.OrgName AS orgName ,F.CompanyGuid AS companyGuid \n" +
                "\t\t\tFROM (\n" +
                "\t\t\t\tSELECT \n" +
                "\t\t\t\t\tSUM ( S.TotalOrder ) AS TotalOrder,SUM ( S.TotalSale ) AS TotalSale,SUM ( S.LogisticsCost ) AS LogisticsCost,SUM ( S.FastMailCost ) AS FastMailCost,\n" +
                "\t\t\t\t\tS.CompanyGuid,S.InfoTime,S.DeptId,S.Prepayment,S.StartSubmitTime,S.TotalSubmitMoney,S.MonthFirstMoney,S.AmazonBack,S.MeritsManage,S.MeritsAllocation,\n" +
                "\t\t\t\t\tS.ProcureCost,S.FirstLegCost,S.StorageCost,S.PackCost,S.MonthDay,S.OrgName\n" +
                "\t\t\t\tFROM TP_ShopAnalysisReportForms S WHERE S.OrgName IS NOT NULL\n" +
                "\t\t\t\tGROUP BY\n" +
                "\t\t\t\t\tS.CompanyGuid,S.InfoTime,S.DeptId,S.Prepayment,S.StartSubmitTime,S.TotalSubmitMoney,S.MonthFirstMoney,S.AmazonBack,S.MeritsManage,S.MeritsAllocation,\n" +
                "\t\t\t\t\tS.ProcureCost,S.FirstLegCost,S.StorageCost,S.PackCost,S.MonthDay,S.OrgName\n" +
                "\t\t\t\t) F \n" +
                "\t\t\t\tGROUP BY F.OrgName,F.MonthDay,F.InfoTime,F.CompanyGuid\n" +
                "\t\t) D\n" +
                "\t\tLEFT JOIN THR_OperateCompanyRoute O ON D.CompanyGuid = O.VGsBm\n" +
                "\t\tLEFT JOIN TS_UserInfo U ON O.CUser = U.UserId \n" +
                "\t\tLEFT JOIN TS_RoleInfo R ON U.PositionGuid = R.Guid \n" +
                "UNION ALL\n" +
                "\tSELECT\n" +
                "\t\tC.TotalOrder,C.TotalSale,C.LogisticsCost,C.FastMailCost,C.Prepayment,C.TotalSubmitMoney,C.MonthFirstMoney,C.AmazonBack,C.MeritsAllocation,C.ProcureCost,\n" +
                "\t\tC.FirstLegCost,C.StorageCost,C.PackCost,'' AS StartSubmitTime,C.MonthDay AS monthDay,\n" +
                "\t\tC.InfoTime,'YSDS' AS orgName ,'' AS CompanyGuid,'' AS operateUser,'' AS userId,'' AS userWork,4 AS pageType,'YSDS' AS vGsJc \n" +
                "\tFROM TP_ShopAnalysisReportFormsCount C \n" +
                "\t) AL";

         */

        String mainTable = "SELECT\n" +
                "\t'' AS Id,'' AS UserId,'' AS ShopGuid,'' AS CompanyGuid,'' AS RecoveryTime,'' AS RecoveryCycle,'' AS MeritsManage,'' AS Status,'' AS CreateTime,'' AS UpdateTime,'' AS deptId,AL.* \n" +
                "FROM (\n" +
                "\tSELECT\n" +
                "\t\tD.*,2 AS pageType \n" +
                "\tFROM (\n" +
                "\t\t\tSELECT \n" +
                "\t\t\t\tSUM ( F.TotalOrder ) AS totalOrder,SUM ( F.TotalSale ) AS totalSale,SUM ( F.LogisticsCost ) AS logisticsCost,SUM ( F.FastMailCost ) AS fastMailCost,\n" +
                "\t\t\t\tSUM ( F.Prepayment ) AS prepayment,SUM ( F.TotalSubmitMoney ) AS totalSubmitMoney,SUM ( F.MonthFirstMoney ) AS monthFirstMoney,SUM ( F.AmazonBack ) AS amazonBack,\n" +
                "\t\t\t\tSUM ( F.MeritsAllocation ) AS meritsAllocation,SUM ( F.ProcureCost ) AS procureCost,SUM ( F.FirstLegCost ) AS firstLegCost,SUM ( F.StorageCost ) AS storageCost,\n" +
                "\t\t\t\tSUM ( F.PackCost ) AS packCost,MIN ( F.StartSubmitTime ) AS startSubmitTime,F.MonthDay AS monthDay,F.InfoTime AS infoTime ,\n" +
                "\t\t\t\tF.OrgName AS orgName \n" +
                "\t\t\tFROM (\n" +
                "\t\t\t\tSELECT \n" +
                "\t\t\t\t\tSUM ( S.TotalOrder ) AS TotalOrder,SUM ( S.TotalSale ) AS TotalSale,SUM ( S.LogisticsCost ) AS LogisticsCost,SUM ( S.FastMailCost ) AS FastMailCost,\n" +
                "\t\t\t\t\tS.CompanyGuid,S.InfoTime,S.DeptId,S.Prepayment,S.StartSubmitTime,S.TotalSubmitMoney,S.MonthFirstMoney,S.AmazonBack,S.MeritsManage,S.MeritsAllocation,\n" +
                "\t\t\t\t\tS.ProcureCost,S.FirstLegCost,S.StorageCost,S.PackCost,S.MonthDay,S.OrgName\n" +
                "\t\t\t\tFROM kjecp_report.dbo.TP_ShopAnalysisReportForms S WHERE S.OrgName IS NOT NULL\n" +
                "\t\t\t\tGROUP BY\n" +
                "\t\t\t\t\tS.CompanyGuid,S.InfoTime,S.DeptId,S.Prepayment,S.StartSubmitTime,S.TotalSubmitMoney,S.MonthFirstMoney,S.AmazonBack,S.MeritsManage,S.MeritsAllocation,\n" +
                "\t\t\t\t\tS.ProcureCost,S.FirstLegCost,S.StorageCost,S.PackCost,S.MonthDay,S.OrgName\n" +
                "\t\t\t\t) F \n" +
                "\t\t\t\tGROUP BY F.OrgName,F.MonthDay,F.InfoTime\n" +
                "\t\t) D\n" +
                "UNION ALL\n" +
                "\tSELECT\n" +
                "\t\tC.TotalOrder,C.TotalSale,C.LogisticsCost,C.FastMailCost,C.Prepayment,C.TotalSubmitMoney,C.MonthFirstMoney,C.AmazonBack,C.MeritsAllocation,C.ProcureCost,\n" +
                "\t\tC.FirstLegCost,C.StorageCost,C.PackCost,'' AS StartSubmitTime,C.MonthDay AS monthDay,\n" +
                "\t\tC.InfoTime,'YSDS' AS orgName ,4 AS pageType\n" +
                "\tFROM kjecp_report.dbo.TP_ShopAnalysisReportFormsCount C \n" +
                "\t) AL ";

        cond.setMainTable("("+mainTable+")");
        //System.out.println(mainTable);
        //cond.addField("A.operateUser");
        //cond.addField("A.userId");
        //cond.addField("A.userWork");
        cond.addField("A.pageType");
        //cond.addField("A.vGsJc");
        cond.addField("A.orgName AS vYyJb");
        //排序
        cond.setOrderby("A.InfoTime DESC, A.TotalOrder DESC");
        //权限
		cond = commonApiService.dataAuth(MENU_ID_BY_USER, cond);
        return  cond;
    }


    //处理返回的数据
    public List<Map<String, Object>> setList(List<Map<String, Object>> resDate,int pageType){
        List<Map<String, Object>> tableDate = new ArrayList();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        //循环处理数据
        for(int i=0;i<resDate.size();i++){
            Map<String, Object> resItem = resDate.get(i);

            String vGsJc = "";
            if(pageType==2){
                String userId = resItem.get("userId").toString();//用户id
                if(userId!=null && !userId.equals("")){
                    ThrOperateCompanyApply thrOperateCompanyApply = new ThrOperateCompanyApply().setCuser(userId).queryFirst();
                    if(thrOperateCompanyApply!=null){
                        vGsJc = thrOperateCompanyApply.getVgsjc()!=null?thrOperateCompanyApply.getVgsjc():"";
                        resItem.put("vGsJc",vGsJc);//简称
                    }
                }
            }

            String dateMonth = resItem.get("infoTime").toString();//统计的月份
            String shopGuid = resItem.get("shopGuid")!=null?resItem.get("shopGuid").toString():"";//店铺表guid
            String companyId = resItem.get("companyGuid")!=null?resItem.get("companyGuid").toString():"";//统计的月份
            String userId = resItem.get("userId")!=null?resItem.get("userId").toString():"";//用户id
            String deptId = resItem.get("deptId")!=null?resItem.get("deptId").toString():"";//组别id
            String orgName = resItem.get("orgName")!=null?resItem.get("orgName").toString():"";//运营机构名称

            Calendar calendar = Calendar.getInstance();
            String oldMonth = "";//上个月
            try {//获取上个月时间
                Date thisDate =sdf.parse(dateMonth);
                calendar.setTime(thisDate);
                calendar.add(Calendar.MONTH, -1);//上个月
                oldMonth = sdf.format(calendar.getTime());
            } catch (ParseException e) {
                e.printStackTrace();
            }

            //获取上个月的统计数据，用来计算环比
            TpShopAnalysisReportForms oldmonthInfo = new TpShopAnalysisReportForms();
            if(pageType==4 ){
                //oldmonthInfo = JSON.parseObject(JSON.toJSONString(resDate.get(1)), TpShopAnalysisReportForms.class);
                oldmonthInfo = tpShopAnalysisReportFormsService.getOldMonthInfo(oldMonth,shopGuid,companyId,userId,pageType,deptId,orgName);
            }
            else if(pageType==2){
                //int pageTypeInfo =pageType;//类型
                int pageTypeInfo = Integer.parseInt(resItem.get("pageType").toString());
                //if(resItem.get("vYyJb").toString().equals("YSDS"))pageTypeInfo =4;
                oldmonthInfo = tpShopAnalysisReportFormsService.getOldMonthInfo(oldMonth,shopGuid,companyId,userId,pageTypeInfo,deptId,orgName);
            }
            else {
                oldmonthInfo = tpShopAnalysisReportFormsService.getOldMonthInfo(oldMonth,shopGuid,companyId,userId,pageType,deptId,orgName);
            }


            float averageOrder = 0;//日均订单
            float oneOrderSale = 0;//单笔订单销售额
            float backProportion = 0;//回款占比 = 月回款/月销售额，低于35%标记红色
            float grossProfit = 0;//毛利润 = 亚马逊回款 - 采购成本 - 头程运费 - 仓储费 - 包装费
            float procureProportion = 0;//采购占比 = 采购成本/店铺回款，高于50%标红
            float firstLegProportion = 0;//头程占比 = 头程运费/店铺回款，高于20%标红
            float fastMailProportion = 0;//快递运费占比 = 快递的运费/头程运费
            float logisticsProportion = 0;//物流运费占比 = 物流的运费/头程运费

            float orderChain = 0;//订单环比 = （本月订单-上月订单）/上月订单
            float saleChain = 0;//销售额环比 =（本月销售额-上月销售额）/上月销售额
            float backChain = 0;//回款环比 =（本月回款-上月回款）/上月回款
            float grossProfitChain = 0;//毛利润环比 = （本月毛利润-上月毛利润）/上月毛利润
            float meritsManageChain = 0;//管理绩效环比 = （本月管理绩效-上月管理绩效）/上月管理绩效
            float meritsAllocationChain = 0;//管理绩效分配环比 = （本月管理绩效-上月管理绩效）/上月管理绩效

            //获取计算需要的数据
            int totalOrder = Integer.parseInt(resItem.get("totalOrder").toString());//总订单数
            BigDecimal totalOrderNum = new BigDecimal(totalOrder);//总订单数
            int monthDay = Integer.parseInt(resItem.get("monthDay").toString());//月天数
            BigDecimal amazonBack = (BigDecimal) resItem.get("amazonBack");//亚马逊回款
            BigDecimal totalSale = (BigDecimal) resItem.get("totalSale");//销售额
            BigDecimal procureCost = (BigDecimal) resItem.get("procureCost");//采购成本
            BigDecimal firstLegCost = (BigDecimal) resItem.get("firstLegCost");//头程运费
            BigDecimal storageCost = (BigDecimal) resItem.get("storageCost");//仓储费
            BigDecimal packCost = (BigDecimal) resItem.get("packCost");//包装费
            BigDecimal fastMailCost = (BigDecimal) resItem.get("fastMailCost");//快递运费
            BigDecimal logisticsCost = (BigDecimal) resItem.get("logisticsCost");//物流运费
            BigDecimal meritsManage = resItem.get("meritsManage")!=null&&!resItem.get("meritsManage").equals("")?(BigDecimal) resItem.get("meritsManage"):BigDecimal.ZERO;//管理绩效
            BigDecimal meritsAllocation = (BigDecimal) resItem.get("meritsAllocation");//管理绩效分配

            String monthDayNew = null;//起始报销周期
            String recoveryCycleNew = null;//回本周期
            //计算起始报销周期、回本周期
            if(resItem.get("firstSaleDay")!=null ){
                String firstSaleDay = resItem.get("firstSaleDay").toString().trim();//开售日期：yyyy-MM
                if(resItem.get("startSubmitTime")!=null ){
                    String startSubmitTime = resItem.get("startSubmitTime").toString().substring(0, 7);//起始报销时间
                    try {
                        monthDayNew = new DateUtil().getMonthSpace(firstSaleDay,startSubmitTime)+"";
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                }
                if(resItem.get("recoveryTime")!=null ){
                    String recoveryTime = resItem.get("recoveryTime").toString().substring(0, 7);//回本时间
                    try {
                        recoveryCycleNew = new DateUtil().getMonthSpace(firstSaleDay,recoveryTime)+"";
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                }
            }

            //计算日均订单
            averageOrder = new BigDecimal(totalOrder).divide(new BigDecimal(monthDay),1,BigDecimal.ROUND_HALF_UP).floatValue();
            //计算单笔订单销售额
            oneOrderSale = (BigDecimal.ZERO).compareTo(totalOrderNum) == 0 ? 0:totalSale.divide(totalOrderNum,4,BigDecimal.ROUND_HALF_UP).floatValue();
            //计算回款占比 = 月回款/月销售额
            backProportion = (BigDecimal.ZERO).compareTo(totalSale) == 0 ? 0:amazonBack.divide(totalSale,4,BigDecimal.ROUND_HALF_UP).floatValue();
            //计算毛利润 = 亚马逊回款 - 采购成本 - 头程运费 - 仓储费 - 包装费
            //BigDecimal grossProfitNum = amazonBack.add(procureCost).subtract(firstLegCost).add(storageCost).add(packCost);
            BigDecimal grossProfitNum = amazonBack.subtract(BigDecimal.ZERO.subtract(procureCost))
                    .subtract(BigDecimal.ZERO.subtract(firstLegCost))
                    .subtract(BigDecimal.ZERO.subtract(storageCost))
                    .subtract(BigDecimal.ZERO.subtract(packCost));
            grossProfit = grossProfitNum.floatValue();
            //计算采购占比 = 采购成本/店铺回款
            procureProportion = (BigDecimal.ZERO).compareTo(amazonBack) == 0 ? 0:procureCost.divide(amazonBack,4,BigDecimal.ROUND_HALF_UP).floatValue();
            //计算头程占比 = 头程运费/店铺回款
            firstLegProportion = (BigDecimal.ZERO).compareTo(amazonBack) == 0 ? 0:firstLegCost.divide(amazonBack,4,BigDecimal.ROUND_HALF_UP).floatValue();
            //计算快递运费占比 = 快递的运费/头程运费
            fastMailProportion = (BigDecimal.ZERO).compareTo(firstLegCost) == 0 ? 0:fastMailCost.divide(firstLegCost,4,BigDecimal.ROUND_HALF_UP).floatValue();
            //计算物流运费占比 = 物流的运费/头程运费
            logisticsProportion = (BigDecimal.ZERO).compareTo(firstLegCost) == 0 ? 0:logisticsCost.divide(firstLegCost,4,BigDecimal.ROUND_HALF_UP).floatValue();

            //计算环比
            if(oldmonthInfo!=null){
                //获取计算需要的上个月数据
                int totalOrderLast = oldmonthInfo.getTotalOrder();//上月总订单数
                BigDecimal totalSaleLast = oldmonthInfo.getTotalSale();//上月销售额
                BigDecimal amazonBackLast = oldmonthInfo.getAmazonBack();//上月亚马逊回款
                BigDecimal procureCostLast = oldmonthInfo.getProcureCost();//上月采购成本
                BigDecimal firstLegCostLast = oldmonthInfo.getFirstLegCost();//上月头程运费
                BigDecimal storageCostLast = oldmonthInfo.getStorageCost();//上月仓储费
                BigDecimal packCostLast = oldmonthInfo.getPackCost();//上月包装费
                BigDecimal meritsManageChainLast = oldmonthInfo.getMeritsManage()!=null?oldmonthInfo.getMeritsManage():BigDecimal.ZERO;//上月管理绩效
                BigDecimal meritsAllocationLast = oldmonthInfo.getMeritsAllocation();//上月管理绩效分配

                //计算订单环比 = （本月订单-上月订单）/上月订单
                orderChain = totalOrderLast!=0?(((float) totalOrder - (float) totalOrderLast)/(float)totalOrderLast):0;
                //计算销售额环比 =（本月销售额-上月销售额）/上月销售额
                saleChain = (BigDecimal.ZERO).compareTo(totalSaleLast) == 0 ? 0:(totalSale.subtract(totalSaleLast)).divide(totalSaleLast,4,BigDecimal.ROUND_HALF_UP).floatValue();
                //计算回款环比 =（本月回款-上月回款）/上月回款
                backChain = (BigDecimal.ZERO).compareTo(amazonBackLast) == 0 ? 0:(amazonBack.subtract(amazonBackLast)).divide(amazonBackLast,4,BigDecimal.ROUND_HALF_UP).floatValue();
                //计算上月毛利润
                BigDecimal grossProfitLast = amazonBackLast.subtract(procureCostLast).subtract(firstLegCostLast).subtract(storageCostLast).subtract(packCostLast);
                //计算毛利润环比 = （本月毛利润-上月毛利润）/上月毛利润
                grossProfitChain = (BigDecimal.ZERO).compareTo(grossProfitLast) == 0 ? 0:(grossProfitNum.subtract(grossProfitLast)).divide(grossProfitLast,4,BigDecimal.ROUND_HALF_UP).floatValue();
                //计算管理绩效环比 = （本月管理绩效-上月管理绩效）/上月管理绩效
                meritsManageChain = (BigDecimal.ZERO).compareTo(meritsManageChainLast) == 0 ? 0:(meritsManage.subtract(meritsManageChainLast)).divide(meritsManageChainLast,4,BigDecimal.ROUND_HALF_UP).floatValue();
                //计算管理绩效分配环比 = （本月管理绩效-上月管理绩效）/上月管理绩效
                meritsAllocationChain = (BigDecimal.ZERO).compareTo(meritsAllocationLast) == 0 ? 0:(meritsAllocation.subtract(meritsAllocationLast)).divide(meritsAllocationLast,4,BigDecimal.ROUND_HALF_UP).floatValue();

            }

            //填入计算的数据
            resItem.put("averageOrder",String.format("%.1f", averageOrder));//日均订单
            resItem.put("orderChain",String.format("%.1f", orderChain*100)+"%");//订单环比 = （本月订单-上月订单）/上月订单
            resItem.put("oneOrderSale",String.format("%.2f", oneOrderSale));//单笔订单销售额
            resItem.put("saleChain",String.format("%.1f", saleChain*100)+"%");//销售额环比 =（本月销售额-上月销售额）/上月销售额
            resItem.put("backChain",String.format("%.1f", backChain*100)+"%");//回款环比 =（本月回款-上月回款）/上月回款
            resItem.put("backProportion",String.format("%.1f", backProportion*100));//回款占比 = 月回款/月销售额，低于35%标记红色
            resItem.put("grossProfit",String.format("%.2f", grossProfit));//毛利润 = 亚马逊回款 - 采购成本 - 头程运费 - 仓储费 - 包装费
            resItem.put("grossProfitChain",String.format("%.1f", grossProfitChain*100)+"%");//毛利润环比 = （本月毛利润-上月毛利润）/上月毛利润
            resItem.put("procureProportion",String.format("%.1f",(0 - procureProportion)*100));//采购占比 = 采购成本/店铺回款，高于50%标红
            resItem.put("firstLegProportion",String.format("%.1f", (0 - firstLegProportion)*100));//头程占比 = 头程运费/店铺回款，高于20%标红
            resItem.put("fastMailProportion",String.format("%.1f", fastMailProportion*100)+"%");//快递运费占比 = 快递的运费/头程运费
            resItem.put("logisticsProportion",String.format("%.1f", logisticsProportion*100)+"%");//物流运费占比 = 物流的运费/头程运费
            resItem.put("meritsManageChain",String.format("%.1f", meritsManageChain*100)+"%");//管理绩效环比 = （本月管理绩效-上月管理绩效）/上月管理绩效
            resItem.put("meritsAllocationChain",String.format("%.1f", meritsAllocationChain*100)+"%");//管理绩效分配环比 = （本月管理绩效-上月管理绩效）/上月管理绩效
            //原本值为负的进行取反，页面显示正数
            resItem.put("totalSubmitMoney",String.format("%.2f", new BigDecimal(0).subtract((BigDecimal) resItem.get("totalSubmitMoney"))));//累计报销金额取反
            //resItem.put("totalSubmitMoney",String.format("%.2f", (BigDecimal) resItem.get("totalSubmitMoney")));//累计报销金额
            resItem.put("procureCost",String.format("%.2f",new BigDecimal(0).subtract((BigDecimal) resItem.get("procureCost"))));//采购成本取反
            resItem.put("firstLegCost",String.format("%.2f",new BigDecimal(0).subtract((BigDecimal) resItem.get("firstLegCost"))));//头程运费取反
            resItem.put("storageCost",String.format("%.2f",new BigDecimal(0).subtract((BigDecimal) resItem.get("storageCost"))));//仓储费取反
            resItem.put("packCost",String.format("%.2f",new BigDecimal(0).subtract((BigDecimal) resItem.get("packCost"))));//包装费取反
            resItem.put("monthDayNew",monthDayNew);//起始报销周期
            resItem.put("recoveryCycleNew",recoveryCycleNew);//回本周期

            tableDate.add(resItem);
        }
        return tableDate;
    }



	/**
     * 查询
     * @param model
     * @param request
     * @param response
     * @param
     * @return
     */
    @RequiresPermissions("tpShopAnalysisReportForms:view")
    @RequestMapping("/search")
    @ResponseBody
    public LayuiTableVoJA search(Model model, HttpServletRequest request, HttpServletResponse response
                                 ,@ModelAttribute TpShopAnalysisReportForms cond
    ){
        try {
            cond = setSeachSql(cond);
            LayuiTableVoJA setList = commonApiService.searchLay(cond);
            //获取
            JSONArray tableVoJAData = setList.getData();

            //转换
            List<Map> resList = JSONArray.parseArray(JSON.toJSONString(tableVoJAData),Map.class);
            List<Map<String, Object>> resListNew = new ArrayList();
            resList.forEach(item-> resListNew.add(item));

            List<Map<String, Object>> resultList = new ArrayList();
            //处理数据
            if(resListNew.size()>0){
                resultList = setList(resListNew,0);
            }
            JSONArray jsonArray = JSONArray.parseArray(JSON.toJSONString(resultList));
            setList.setData(jsonArray);

            return setList;
        }catch (Exception e){
            logger.error("查询运营分析表数据出错：",e);
            LayuiTableVoJA setList = new LayuiTableVoJA();
            setList.setCode(1);
            setList.setMsg("查询出错，请稍后重试！");
            return setList;
        }
    }


    /**
     *导出
     * @param cond
     * @return
     */
    @RequestMapping("/export")
    @ResponseBody
    public R export(@ModelAttribute TpShopAnalysisReportForms cond){
        try {
            cond.setPage(0).setSize(999999);
            cond = setSeachSql(cond);
            //获取数据
            List<Map<String, Object>> listData = cond.queryMap();
            //处理数据
            listData = setList(listData,0);
            //获取菜单字段数据
            List<YsCol> listField = new YsCol()
                    .setMenuId(MENU_ID_BY_SHOP)
                    .where("A.Code != 'ope'")
                    .setOrderby("A.SortNo asc")
                    .queryList();

            ExportExcelUtils<TpShopAnalysisReportForms> util = new ExportExcelUtils<>(new TpShopAnalysisReportForms());
            String toFilePath = util.exportExcel(listData, listField, "店铺运营分析表(按店铺)");
            UploadService.UploadDto dto = commonApiService.uploadLocalToServer(toFilePath);
            return R.ok(dto.getUrl());
            //return util.exportExcel(listData, listField, "店铺运营分析表(按店铺)");
        }catch (Exception e){
            logger.error("导出运营分析表数据出错：",e);
            return R.error("导出失败，请稍后重试！");
        }
    }

    /**
     * 查询
     * @param model
     * @param request
     * @param response
     * @param
     * @return exportByCompany
     */
    @RequiresPermissions("tpShopAnalysisReportFormsByCompany:view")
    @RequestMapping("/searchByCompany")
    @ResponseBody
    public LayuiTableVoJA searchByCompany(Model model, HttpServletRequest request, HttpServletResponse response
            ,@ModelAttribute TpShopAnalysisReportForms cond
    ){
        try {
            cond = setSeachSqlByCompany(cond);
            LayuiTableVoJA setList = commonApiService.searchLay(cond);
            //获取
            JSONArray tableVoJAData = setList.getData();
            //转换
            List<Map> resList = JSONArray.parseArray(JSON.toJSONString(tableVoJAData),Map.class);
            List<Map<String, Object>> resListNew = new ArrayList();
            resList.forEach(item-> resListNew.add(item));
            //处理数据
            List<Map<String, Object>> resultList = new ArrayList();
            //处理数据
            if(resListNew.size()>0){
                resultList = setList(resListNew,1);
            }
            JSONArray jsonArray = JSONArray.parseArray(JSON.toJSONString(resultList));
            setList.setData(jsonArray);
            return setList;
        }catch (Exception e){
            logger.error("查询运营分析表数据出错：",e);
            LayuiTableVoJA setList = new LayuiTableVoJA();
            setList.setCode(1);
            setList.setMsg("查询出错，请稍后重试！");
            return setList;
        }
    }


    /**
     *导出
     * @param cond
     * @return
     */
    @RequestMapping("/exportByCompany")
    @ResponseBody
    public R exportByCompany(@ModelAttribute TpShopAnalysisReportForms cond){
        try {
            cond.setPage(0).setSize(999999);
            cond = setSeachSqlByCompany(cond);
            //获取数据
            List<Map<String, Object>> listData = cond.queryMap();
            //处理数据
            listData = setList(listData,1);
            //获取菜单字段数据
            List<YsCol> listField = new YsCol()
                    .setMenuId(MENU_ID_BY_COMPANY)
                    .where("A.Code != 'ope'")
                    .setOrderby("A.SortNo asc")
                    .queryList();

            ExportExcelUtils<TpShopAnalysisReportForms> util = new ExportExcelUtils<>(new TpShopAnalysisReportForms());
            //return util.exportExcel(listData, listField, "店铺运营分析表(按公司)");
            String toFilePath = util.exportExcel(listData, listField, "店铺运营分析表(按公司)");
            UploadService.UploadDto dto = commonApiService.uploadLocalToServer(toFilePath);
            return R.ok(dto.getUrl());
        }catch (Exception e){
            logger.error("导出运营分析表数据出错：",e);
            return R.error("导出失败，请稍后重试！");
        }
    }



    /**
     * 查询
     * @param model
     * @param request
     * @param response
     * @param
     * @return exportByCompany
     */
    @RequiresPermissions("tpShopAnalysisReportFormsByUser:view")
    @RequestMapping("/searchByUser")
    @ResponseBody
    public LayuiTableVoJA searchByUser(Model model, HttpServletRequest request, HttpServletResponse response
            ,@ModelAttribute TpShopAnalysisReportForms cond
    ){
        try {

            List<Map<String, Object>> resultList = new ArrayList();
            cond = setSeachSqlByMainUser(cond);
            LayuiTableVoJA setList = commonApiService.searchLay(cond);
            //获取
            JSONArray tableVoJAData = setList.getData();
            //转换
            List<Map> resList = JSONArray.parseArray(JSON.toJSONString(tableVoJAData),Map.class);
            List<Map<String, Object>> resListNew = new ArrayList();
            resList.forEach(item-> resListNew.add(item));
            //处理数据
            if(resListNew.size()>0){
                resultList = setList(resListNew,2);
            }
            JSONArray jsonArray = JSONArray.parseArray(JSON.toJSONString(resultList));
            setList.setData(jsonArray);
            return setList;

        }catch (Exception e){
            logger.error("查询运营分析表数据出错：",e);
            LayuiTableVoJA setList = new LayuiTableVoJA();
            setList.setCode(1);
            setList.setMsg("查询出错，请稍后重试！");
            return setList;
        }
    }





    /**
     *导出
     * @param cond
     * @return
     */
    @RequestMapping("/exportByUser")
    @ResponseBody
    public R exportByUser(@ModelAttribute TpShopAnalysisReportForms cond){
        try {
            cond.setPage(1).setSize(999999);
            cond = setSeachSqlByMainUser(cond);
            List<Map<String, Object>> resultList = new ArrayList();
            LayuiTableVoJA setList = commonApiService.searchLay(cond);
            //获取
            JSONArray tableVoJAData = setList.getData();
            //转换
            List<Map> resList = JSONArray.parseArray(JSON.toJSONString(tableVoJAData),Map.class);
            List<Map<String, Object>> resListNew = new ArrayList();
            resList.forEach(item-> resListNew.add(item));
            //处理数据
            if(resListNew.size()>0){
                resultList = setList(resListNew,2);
            }
            //JSONArray jsonArray = JSONArray.parseArray(JSON.toJSONString(resultList));
            //setList.setData(jsonArray);
            //处理数据
            //listData = setList(listData,2);
            //获取菜单字段数据
            List<YsCol> listField = new YsCol()
                    .setMenuId(MENU_ID_BY_USER)
                    .where("A.Code != 'ope'")
                    .setOrderby("A.SortNo asc")
                    .queryList();

            ExportExcelUtils<TpShopAnalysisReportForms> util = new ExportExcelUtils<>(new TpShopAnalysisReportForms());
            //return util.exportExcel(listData, listField, "店铺运营分析表(按组别)");
            String toFilePath = util.exportExcel(resultList, listField, "店铺运营分析表(按组别)");
            UploadService.UploadDto dto = commonApiService.uploadLocalToServer(toFilePath);
            return R.ok(dto.getUrl());
        }catch (Exception e){
            logger.error("导出运营分析表数据出错：",e);
            return R.error("导出失败，请稍后重试！");
        }
    }

}


/*SELECT
		A.*,
		U.UserId + '/' + U.UserName AS operateUser,
		U.DeptName AS vYyJb,
		U.UserId AS userId,
		'运营组长' AS userWork,
		1 AS pageType
	FROM
		(
		SELECT SUM
			( F.TotalOrder ) AS totalOrder,
			SUM ( F.TotalSale ) AS totalSale,
			SUM ( F.LogisticsCost ) AS logisticsCost,
			SUM ( F.FastMailCost ) AS fastMailCost,
			SUM ( F.Prepayment ) AS prepayment,
			SUM ( F.TotalSubmitMoney ) AS totalSubmitMoney,
			SUM ( F.MonthFirstMoney ) AS monthFirstMoney,
			SUM ( F.AmazonBack ) AS amazonBack,
			SUM ( F.MeritsAllocation ) AS meritsAllocation,
			SUM ( F.ProcureCost ) AS procureCost,
			SUM ( F.FirstLegCost ) AS firstLegCost,
			SUM ( F.StorageCost ) AS storageCost,
			SUM ( F.PackCost ) AS packCost,
			MIN ( F.StartSubmitTime ) AS startSubmitTime,
			F.MonthDay AS monthDay,
			F.DeptId AS deptId,
			F.InfoTime AS infoTime
		FROM
			(
			SELECT SUM
				( S.TotalOrder ) AS TotalOrder,
				SUM ( S.TotalSale ) AS TotalSale,
				SUM ( S.LogisticsCost ) AS LogisticsCost,
				SUM ( S.FastMailCost ) AS FastMailCost,
				S.CompanyGuid,
				S.InfoTime,
				S.DeptId,
				S.Prepayment,
				S.StartSubmitTime,
				S.TotalSubmitMoney,
				S.MonthFirstMoney,
				S.AmazonBack,
				S.MeritsManage,
				S.MeritsAllocation,
				S.ProcureCost,
				S.FirstLegCost,
				S.StorageCost,
				S.PackCost,
				S.MonthDay
			FROM
				TP_ShopAnalysisReportForms S
			GROUP BY
				S.CompanyGuid,
				S.InfoTime,
				S.DeptId,
				S.Prepayment,
				S.StartSubmitTime,
				S.TotalSubmitMoney,
				S.MonthFirstMoney,
				S.AmazonBack,
				S.MeritsManage,
				S.MeritsAllocation,
				S.ProcureCost,
				S.FirstLegCost,
				S.StorageCost,
				S.PackCost,
				S.MonthDay
			) F
		GROUP BY
			F.DeptId ,
			F.MonthDay,
			F.InfoTime
		) A
		LEFT JOIN TS_UserInfo U ON A.DeptId = U.DeptId
		LEFT JOIN TS_RoleInfo R ON U.PositionGuid = R.Guid
	WHERE
		U.GuidYsRole = '运营组长'
		AND R.RoleName= '运营组长' UNION ALL
	SELECT
		C.TotalOrder,
		C.TotalSale,
		C.LogisticsCost,
		C.FastMailCost,
		C.Prepayment,
		C.TotalSubmitMoney,
		C.MonthFirstMoney,
		C.AmazonBack,
		C.MeritsAllocation,
		C.ProcureCost,
		C.FirstLegCost,
		C.StorageCost,
		C.PackCost,
		'' AS StartSubmitTime,
		C.MonthDay AS monthDay,
		'' AS deptId,
		C.InfoTime,
		'' AS operateUser,
		'YSDS' AS vYyJb,
		'' AS userId,
		'运营组长' AS userWork,
		4 AS pageType
	FROM
		TP_ShopAnalysisReportFormsCount C
		*/